package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.model.Class;
import com.dao.ClassDao;
import com.dao.StudentInfoDao;
import com.jspsmart.upload.Files;
import com.jspsmart.upload.SmartUpload;
import com.model.StudentInfo;
import com.util.ModeStudent;
import com.util.StringUtil;

import jxl.*;
import jxl.biff.FontRecord;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.WritableFont.FontName;

/**
 * Servlet implementation class StuServlet
 */
@WebServlet("/StuServlet")
public class StuServlet extends HttpServlet {
	StudentInfoDao sid=new StudentInfoDao();
	ClassDao cd=new ClassDao();

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		String op=request.getParameter("op");

		if("select".equals(op)) {
			select(request,response);
		}else if("update".equals(op)) {
			update(request,response);
		}else if("delete".equals(op)) {
			delete(request,response);
		}else if("add".equals(op)) {
			add(request,response);
		}else if("adds".equals(op)) {
			adds(request,response);
		}else if("导入模版".equals(op)){
			drmb(request,response);
		}else if("导出".equals(op)) {
			dc(request,response);
		}else if("xiugai".equals(op)) {
			int id=((StudentInfo)request.getSession().getAttribute("model")).getStuId();
			String pwd=request.getParameter("pwd");
			if(sid.updatepwdById(pwd, id)==1) {
				response.getWriter().print("修改成功！");
			}else {
				response.getWriter().print("修改有误！");
			}
		}
		
		request.getSession().getAttribute("student");
	}
	
	
	protected void select(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		int page=Integer.parseInt(request.getParameter("page"));
		int rows=Integer.parseInt(request.getParameter("rows"));
		String name=request.getParameter("name");
		int bj=0;
		if(request.getParameter("bj")!=null) {
			bj=Integer.parseInt(request.getParameter("bj"));
		}
		ModeStudent m=sid.selectAll(page, rows, name, bj);
		response.getWriter().print(JSON.toJSONString(m));
	}

	protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		StudentInfo stu=new StudentInfo();
		stu.setClassId(cd.selectIdByName(request.getParameter("classname")));
		stu.setStuId(Integer.parseInt(request.getParameter("stuid")));
		stu.setStuName(request.getParameter("stuname"));
		stu.setStuPhone(request.getParameter("stuphone"));
		stu.setStuPwd(request.getParameter("stupwd"));
		stu.setStuSex(request.getParameter("stusex"));
		stu.setStuStatus(request.getParameter("stustatus"));
		if(sid.updateBuId(stu)==1) {
			response.getWriter().print("修改成功");
		}
		else {
			response.getWriter().print("修改失败");
		}
	}
	
	protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		int id=Integer.parseInt(request.getParameter("id"));
		if(sid.deleteById(id)==1) {
			response.getWriter().print("删除成功");
		}else {
			response.getWriter().print("删除失败");
		}
	}
	
	protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		StudentInfo stu=new StudentInfo();
		stu.setClassId(Integer.parseInt(request.getParameter("classname")));
		stu.setStuName(request.getParameter("stuname"));
		stu.setStuPhone(request.getParameter("stuphone"));
		stu.setStuPwd(request.getParameter("stupwd"));
		stu.setStuSex(request.getParameter("stusex"));
		stu.setStuStatus(request.getParameter("stustatus"));
		
		if(sid.insertAll(stu)==1) {
			response.getWriter().print("添加成功");
		}
		else {
			response.getWriter().print("添加失败");
		}
	}
	
	protected void adds(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("GBK");
		
		//定义文件名
				String fileName=StringUtil.getFileName();
				SmartUpload su=new SmartUpload();
				su.initialize(getServletConfig(), request, response);
				try {
					su.upload();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
					int count=0;
					Files fs=su.getFiles();
					com.jspsmart.upload.File f=fs.getFile(0);
					if("".equals(f.getFileName())||f.getFileName()==null) {
						response.getWriter().print("请先选择文件。");
					}
					try {
						//把文件另存为
						f.saveAs("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/"+f.getFileName());
						
						//找到工作簿
						Workbook book=Workbook.getWorkbook(new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/"+f.getFileName()));
						//找到工作表
						Sheet st=book.getSheet(0);
						Cell[] cel = null;
						//通过循环，找到每行，然后在读每行的列
						for(int i=2;i<st.getRows();i++) {
							//创建实体类
							StudentInfo stu=new StudentInfo();
							//i表示的是行的下标
							cel=st.getRow(i);
							stu.setStuName(cel[0].getContents());
							stu.setStuPwd(cel[1].getContents());
							stu.setClassId(Integer.parseInt(""+cd.selectIdByName(cel[2].getContents())));
							stu.setStuSex(cel[3].getContents());
							stu.setStuPhone(cel[4].getContents());
							stu.setStuStatus(cel[5].getContents());
							//添加到数据库
							sid.insertAll(stu);
							count++;
						}
						//关闭
						book.close();
						if(count>0){
							response.getWriter().print("导入成功");
						}else {
							response.getWriter().print("导入失败,请检查后重试！");
						}
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
	
	protected void drmb(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("GBK");
		response.setCharacterEncoding("GBK");
		
		SmartUpload su=new SmartUpload();
		su.initialize(getServletConfig(), request, response);
		try {
			su.upload();
			su.downloadFile("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/学生模版.xls");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	protected void dc(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("GBK");
		response.setCharacterEncoding("GBK");
		String classname=request.getParameter("classnames");
		SmartUpload su=new SmartUpload();
		su.initialize(getServletConfig(), request, response);
		try {
			su.upload();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//定义File表示文件
		java.io.File f = new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student");
		//判断f锁表示的目录是否存在
		if(!f.exists())
		{
					// 创建这个目录
					f.mkdir();
		}
		java.io.File f1 = new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls");
		//判断文件是否存在
		if(!f1.exists())
		{
				try {
					f1.createNewFile();
				} catch (IOException e) {
						// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		//创建工作簿
		WritableWorkbook book = Workbook.createWorkbook(new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls"));
		
		
		
		//把导入.xls里的数据写到导出.xls表里
		try {
			
			if(classname.equals("全部班级")) {
				try {
					//存在学生的班级
					ArrayList<Class> cla=sid.selectByClassName();
					//所有学生信息
					ArrayList<StudentInfo> stu=sid.selectByQbXueSheng();
					//有数据的班级总数
					int count=sid.selectByQbBjCount();
					//设置单元格的字体
					WritableFont font1=new WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD);
					WritableFont font2=new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
					WritableFont font3=new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD);
					//设置单元格的格式
					WritableCellFormat format1=new WritableCellFormat(font1);
					format1.setAlignment(Alignment.CENTRE);
					//设置边框
					format1.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format2=new WritableCellFormat(font2);
					format2.setAlignment(Alignment.CENTRE);
					//设置边框
					format2.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format3=new WritableCellFormat(font3);
					//设置边框
					format3.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					int row=0;
					for(int i=0;i<count;i++) {
					
						//创建工作表 
						WritableSheet sheet=book.createSheet(cla.get(i).getClassName()+"",i); 

						//设置单元格的大小
						sheet.mergeCells(0, 0, 5, 0);
						sheet.setColumnView(5, 28);
						
						//添加
						sheet.addCell(new Label(0,0,cla.get(i).getClassName()+"学生信息表",format1));
						
						sheet.addCell(new Label(0,1,"姓名",format2));
						sheet.addCell(new Label(1,1,"密码",format2));
						sheet.addCell(new Label(2,1,"班级",format2));
						sheet.addCell(new Label(3,1,"性别",format2));
						sheet.addCell(new Label(4,1,"联系电话",format2));
						sheet.addCell(new Label(5,1,"身份证号",format2));
						int k=0;
						for(int j=row;j<stu.size();j++) {
							if(!cla.get(i).getClassName().equals(stu.get(j).getClassName())) {
								row=j;
								break;
							}
							sheet.addCell(new Label(0,k+2,stu.get(j).getStuName()+"",format3));
							sheet.addCell(new Label(1,k+2,stu.get(j).getStuPwd(),format3));
							sheet.addCell(new Label(2,k+2,stu.get(j).getClassName()+"",format3));
							sheet.addCell(new Label(3,k+2,stu.get(j).getStuSex()+"",format3));
							sheet.addCell(new Label(4,k+2,stu.get(j).getStuPhone()+"",format3));
							sheet.addCell(new Label(5,k+2,stu.get(j).getStuStatus()+"",format3));
							row=j;
							k++;
						}
					}
					//写
					book.write(); 
					//关 
					book.close();
					//下载
					try {
						su.downloadFile("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls");
						System.out.println("xz");
						request.getSession().setAttribute("xz", "1");
						response.getWriter().print("success");
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
				
			}else {
				try {
					
					//创建工作表 
					WritableSheet sheet=book.createSheet(classname+"",0); 
					
					
					//设置单元格的大小
					sheet.mergeCells(0, 0, 5, 0);
					sheet.setColumnView(5, 28);
					//设置单元格的字体
					WritableFont font1=new WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD);
					WritableFont font2=new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
					WritableFont font3=new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD);
					//设置单元格的格式
					WritableCellFormat format1=new WritableCellFormat(font1);
					format1.setAlignment(Alignment.CENTRE);
					//设置边框
					format1.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format2=new WritableCellFormat(font2);
					format2.setAlignment(Alignment.CENTRE);
					//设置边框
					format2.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format3=new WritableCellFormat(font3);
					//设置边框
					format3.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					
					//添加
					sheet.addCell(new Label(0,0,classname+"学生信息表",format1));
					
					sheet.addCell(new Label(0,1,"姓名",format2));
					sheet.addCell(new Label(1,1,"密码",format2));
					sheet.addCell(new Label(2,1,"班级",format2));
					sheet.addCell(new Label(3,1,"性别",format2));
					sheet.addCell(new Label(4,1,"联系电话",format2));
					sheet.addCell(new Label(5,1,"身份证号",format2));
					
					
					
					ArrayList<StudentInfo> list=sid.selectAll(classname);
					for(int i=0;i<list.size();i++) {
						sheet.addCell(new Label(0,i+2,list.get(i).getStuName()+"",format3));
						sheet.addCell(new Label(1,i+2,list.get(i).getStuPwd(),format3));
						sheet.addCell(new Label(2,i+2,list.get(i).getClassName()+"",format3));
						sheet.addCell(new Label(3,i+2,list.get(i).getStuSex()+"",format3));
						sheet.addCell(new Label(4,i+2,list.get(i).getStuPhone()+"",format3));
						sheet.addCell(new Label(5,i+2,list.get(i).getStuStatus()+"",format3));
					}
					
					//写
					book.write(); 
			
					//关 
					book.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
					//下载
					try {
						su.downloadFile("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls");

						System.out.println("xz");
						request.getSession().setAttribute("xz", "1");
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}


